# coding:utf-8
from email.mime.text import MIMEText
import pymysql
import pandas as pd
from email.mime.multipart import MIMEMultipart
import smtplib
import time
import datetime


class AccountEmail():

    def __init__(self):
        HOST = 'oyo-scrapy-database.mysql.rds.aliyuncs.com'
        PORT = 3306
        USER = 'scrapy'
        PASSWD = 'Podf&T$2'
        DB = 'spider'
        CHARSET = 'utf8mb4'

        self.conn = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWD, db=DB, charset=CHARSET)
        self.cur = self.conn.cursor()


    def check(self,batch_no):

        qunar_sql = "select count(*) from ota_back_account_info where ota_channel_en='qunar' and batch_no='%s' and verify_time is null"%(batch_no)
        self.cur.execute(qunar_sql)
        qunar_num = self.cur.fetchall()
        qunar_num=qunar_num[0][0]

        qunar_sql2 = "select count(*) from ota_back_account_info where ota_channel_en='qunar' and batch_no='%s'" % (batch_no)
        self.cur.execute(qunar_sql2)
        qunar_num2 = self.cur.fetchall()
        qunar_num2 = qunar_num2[0][0]

        elong_sql = "select count(*) from ota_back_account_info where ota_channel_en='elong' and batch_no='%s' and verify_time is null"%(batch_no)
        self.cur.execute(elong_sql)
        elong_num = self.cur.fetchall()
        elong_num=elong_num[0][0]

        elong_sql2 = "select count(*) from ota_back_account_info where ota_channel_en='elong' and batch_no='%s'" % (batch_no)
        self.cur.execute(elong_sql2)
        elong_num2 = self.cur.fetchall()
        elong_num2 = elong_num2[0][0]

        ctrip_sql = "select count(*) from ota_back_account_info where ota_channel_en='ctrip' and batch_no='%s' and verify_time is null"%(batch_no)
        self.cur.execute(ctrip_sql)
        ctrip_num = self.cur.fetchall()
        ctrip_num=ctrip_num[0][0]

        ctrip_sql2 = "select count(*) from ota_back_account_info where ota_channel_en='ctrip' and batch_no='%s'" % (batch_no)
        self.cur.execute(ctrip_sql2)
        ctrip_num2 = self.cur.fetchall()
        ctrip_num2 = ctrip_num2[0][0]

        meituan_sql = "select count(*) from ota_back_account_info where ota_channel_en='meituan' and batch_no='%s' and verify_time is null"%(batch_no)
        self.cur.execute(meituan_sql)
        meituan_num = self.cur.fetchall()
        meituan_num=meituan_num[0][0]

        meituan_sql2 = "select count(*) from ota_back_account_info where ota_channel_en='meituan' and batch_no='%s'" % (batch_no)
        self.cur.execute(meituan_sql2)
        meituan_num2 = self.cur.fetchall()
        meituan_num2 = meituan_num2[0][0]

        if qunar_num2 == 0 and elong_num2 == 0 and ctrip_num2 == 0 and meituan_num2 == 0:
            print('无验证数据')
            return

        if qunar_num==0 and elong_num==0 and ctrip_num==0 and meituan_num==0:
            print('核对通过')

            # 导出excel美团
            sql_query = """
            SELECT
                a.sync_account_id,
                a.sync_username,
                a.sync_password,
                a.sync_status,
                a.sync_group_account,
                a.sync_ota_channel_en,
                a.ota_channel_en,
                a.create_time,
                a.verify_time,
            CASE
                b.Meituan_Status 
                WHEN 0 THEN
                '成功' 
                WHEN 1 THEN
                '失败' 
                WHEN 2 THEN
                '成功' 
                WHEN 3 THEN
                '成功' 
                WHEN 5 THEN
                '成功' 
                END AS '状态',
                b.Meituan_Status,
                b.Meituan_Date,
                a.batch_no 
            FROM
                ota_back_account_info AS a,
                ota_back_account_status AS b 
            WHERE
                a.CRS_ID = b.CRS_ID 
                AND a.batch_no = '%s' 
                AND a.ota_channel_en = 'meituan'
                """%(batch_no)
            df = pd.read_sql(sql_query, con=self.conn)
            excel_writer='ota_back_account_status_meituan_%s.xlsx'%(batch_no)
            df.to_excel(excel_writer, sheet_name='account', na_rep='', float_format=None, columns=None, header=True, index=False, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)



            # 导出excel携程
            sql_query = """
                        SELECT
                            a.sync_account_id,
                            a.sync_username,
                            a.sync_password,
                            a.sync_status,
                            a.sync_group_account,
                            a.sync_ota_channel_en,
                            a.ota_channel_en,
                            a.create_time,
                            a.verify_time,
                        CASE
                            b.Ctrip_Status 
                            WHEN 0 THEN
                            '成功' 
                            WHEN 1 THEN
                            '失败' 
                            WHEN 2 THEN
                            '成功' 
                            END AS '状态',
                            b.Ctrip_Status,
                            b.Ctrip_Date,
                            a.batch_no 
                        FROM
                            ota_back_account_info AS a,
                            ota_back_account_status AS b 
                        WHERE
                            a.CRS_ID = b.CRS_ID 
                            AND a.batch_no = '%s' 
                            AND a.ota_channel_en = 'ctrip'
                            """ % (batch_no)
            df = pd.read_sql(sql_query, con=self.conn)
            excel_writer = 'ota_back_account_status_ctrip_%s.xlsx' % (batch_no)
            df.to_excel(excel_writer, sheet_name='account', na_rep='', float_format=None, columns=None, header=True,
                        index=False, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True,
                        encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

            # 导出excel艺龙
            sql_query = """
                        SELECT
                            a.sync_account_id,
                            a.sync_username,
                            a.sync_password,
                            a.sync_status,
                            a.sync_group_account,
                            a.sync_ota_channel_en,
                            a.ota_channel_en,
                            a.create_time,
                            a.verify_time,
                        CASE
                            b.Elong_Status 
                            WHEN 0 THEN
                            '成功' 
                            WHEN 1 THEN
                            '失败' 
                            WHEN 2 THEN
                            '成功' 
                            END AS '状态',
                            b.Elong_Status,
                            b.Elong_Date,
                            a.batch_no 
                        FROM
                            ota_back_account_info AS a,
                            ota_back_account_status AS b 
                        WHERE
                            a.CRS_ID = b.CRS_ID 
                            AND a.batch_no = '%s' 
                            AND a.ota_channel_en = 'elong'
                            """ % (batch_no)
            df = pd.read_sql(sql_query, con=self.conn)
            excel_writer = 'ota_back_account_status_elong_%s.xlsx' % (batch_no)
            df.to_excel(excel_writer, sheet_name='account', na_rep='', float_format=None, columns=None, header=True,
                        index=False, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True,
                        encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)

            # 导出excel去哪儿
            sql_query = """
                        SELECT
                            a.sync_account_id,
                            a.sync_username,
                            a.sync_password,
                            a.sync_status,
                            a.sync_group_account,
                            a.sync_ota_channel_en,
                            a.ota_channel_en,
                            a.create_time,
                            a.verify_time,
                        CASE
                            b.Qunar_Status 
                            WHEN 0 THEN
                            '成功' 
                            WHEN 1 THEN
                            '失败' 
                            WHEN 2 THEN
                            '未知' 
                            END AS '状态',
                            b.Qunar_Status,
                            b.Qunar_Date,
                            a.batch_no 
                        FROM
                            ota_back_account_info AS a,
                            ota_back_account_status AS b 
                        WHERE
                            a.CRS_ID = b.CRS_ID 
                            AND a.batch_no = '%s' 
                            AND a.ota_channel_en = 'qunar'
                            """ % (batch_no)
            df = pd.read_sql(sql_query, con=self.conn)
            excel_writer = 'ota_back_account_status_qunar_%s.xlsx' % (batch_no)
            df.to_excel(excel_writer, sheet_name='account', na_rep='', float_format=None, columns=None, header=True,
                        index=False, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True,
                        encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)




            to_list=[
                'yimin.zhang@oyohotels.cn',
                 'james.hu@oyohotels.cn',
                 'hongying.liao@oyohotels.cn',
                 'cheryl.cheng@oyohotels.cn',
                 'joy.he1@oyohotels.cn',
                 'wheat.kong@oyohotels.cn',
                 'bob.bao@oyohotels.cn',
                 'erjia.fu@oyohotels.cn',
                 'yanlun.tang@oyohotels.cn'
                     ] # 收件人
            cc_list=[] # 抄送
            sub='ota账号后台验证-全量'+str(batch_no) # 主题
            file=['ota_back_account_status_meituan_%s.xlsx'%(batch_no),'ota_back_account_status_ctrip_%s.xlsx'%(batch_no),'ota_back_account_status_elong_%s.xlsx'%(batch_no),'ota_back_account_status_qunar_%s.xlsx'%(batch_no)] # 附件
            mail_user='yimin.zhang@oyohotels.cn' #发送人
            mail_pass='Bvfg9603' # 密码
            self.send_mail(to_list=to_list,cc_list=cc_list,sub=sub,file=file,mail_user=mail_user,mail_pass=mail_pass,batch_no=batch_no)

        else:
            print('核对未通过')

    def send_mail(self,to_list, cc_list, sub, file, mail_user, mail_pass,batch_no):
        ''' 发送最低价email '''
        # mail_user = conf.get('email', 'mail_user')  # 用户名
        # mail_pass = conf.get('email', 'mail_pass')  # 口令
        print("邮件发送开始！！from:%s to:%s" % (mail_user, to_list))
        # mail_host = "smtp.mxhichina.com"  # 设置服务器
        mail_host = "smtp.qiye.aliyun.com"
        mail_port = 465
        mail_postfix = "oyohotels.com"  # 发件箱的后缀

        me = "Big Data" + "<" + mail_user + ">"

        msg = MIMEMultipart()
        msg['Subject'] = sub  # 邮件主题
        msg['From'] = me
        msg['To'] = ";".join(to_list)
        if not cc_list:
            msg['Cc'] = ';'.join(cc_list)

        # 添加内容
        # text = "Dear all!\n This is the dynamic pricing result for the MG Franchise hotel."
        # text_plain = MIMEText(text, 'plain', 'utf-8')
        # msg.attach(text_plain)

        # 添加 附加
        for fi in file:
            attach_name = fi
            att1 = MIMEText(open(fi, 'rb').read(), 'xlsx', 'gb2312')
            att1["Content-Type"] = 'application/vnd.ms-excel; charset=utf-8'
            att1["Content-Disposition"] = 'attachment;filename=' + attach_name
            msg.attach(att1)

        # # file.to_excel('log/' + attach_name)
        # attach_name = "ota_back_account_status_%s.xlsx"%(batch_no)
        # att1 = MIMEText(open(file, 'rb').read(), 'xls', 'gb2312')
        # att1["Content-Type"] = 'application/vnd.ms-excel; charset=utf-8'
        # att1["Content-Disposition"] = 'attachment;filename=' + attach_name
        # msg.attach(att1)

        try:
            server = smtplib.SMTP_SSL(mail_host, mail_port)
            # server.starttls()
            server.set_debuglevel(1)
            server.login(mail_user, mail_pass)
            print("邮件登录验证成功")
            server.sendmail(me, to_list, msg.as_string())
            server.close()
            # logging.debug("邮件发送失败！！")
            return True
        except Exception as e:
            print("邮件发送失败！！")
            print(e)
            return False



if __name__ == '__main__':
    # now_time = datetime.datetime.now()
    # yesterday = now_time + datetime.timedelta(days=-1)
    # batch_no = yesterday.strftime('%Y%m%d') + '21'
    # batch_no = time.strftime('%Y%m%d%H', time.localtime(time.time()))
    batch_no = '2019030821'
    begin=AccountEmail()
    begin.check(batch_no)
